﻿namespace PropOnline.Stores.Data
{
    using System;
    using System.Collections.Generic;
    using System.Configuration;
    using System.Data;
    using System.Data.Common;
    using MySql.Data.MySqlClient;

    public class Database
    {
        private CommandType commandType;
        private string commandText;
        private List<MySqlParameter> parameters = new List<MySqlParameter>();

        private Database()
        { 
        }

        public static Database Query(string queryText)
        {
            var database = new Database();
            database.commandType = CommandType.Text;
            database.commandText = queryText;
            return database;
        }

        public static Database StoredProcedure(string procedureName)
        {
            var database = new Database();
            database.commandType = CommandType.StoredProcedure;
            database.commandText = procedureName;
            return database;
        }

        public void Execute()
        {
            if (string.IsNullOrEmpty(this.commandText))
            {
                throw new ArgumentException("Call the method Query or StoredProcedure before calling Results.");
            }

            var connectionString = ConfigurationManager.ConnectionStrings["PropOnline"].ConnectionString;
            using (MySqlConnection sqlConnection = new MySqlConnection(connectionString))
            using (MySqlCommand sqlCommand = sqlConnection.CreateCommand())
            {
                sqlCommand.CommandType = this.commandType;
                sqlCommand.CommandText = this.commandText;

                this.parameters.ForEach(p => sqlCommand.Parameters.Add(p));

                sqlConnection.Open();
                sqlCommand.ExecuteNonQuery();
            }
        }

        public IEnumerable<dynamic> Results()
        {
            if (string.IsNullOrEmpty(this.commandText))
            {
                throw new ArgumentException("Call the method Query or StoredProcedure before calling Results.");
            }

            var connectionString = ConfigurationManager.ConnectionStrings["PropOnline"].ConnectionString;
            using (MySqlConnection sqlConnection = new MySqlConnection(connectionString))
            using (MySqlCommand sqlCommand = sqlConnection.CreateCommand())
            {
                sqlCommand.CommandType = this.commandType;
                sqlCommand.CommandText = this.commandText;

                this.parameters.ForEach(p => sqlCommand.Parameters.Add(p));

                sqlConnection.Open();

                var reader = sqlCommand.ExecuteReader();
                while (reader.Read())
                {
                    yield return new DynamicDictionary(reader);
                }
            }
        }

        public IEnumerable<T> Results<T>(Func<dynamic, T> transformResults)
        {
            if (string.IsNullOrEmpty(this.commandText))
            {
                throw new ArgumentException("Call the method Query or StoredProcedure before calling Results.");
            }

            var connectionString = ConfigurationManager.ConnectionStrings["PropOnline"].ConnectionString;
            using (MySqlConnection sqlConnection = new MySqlConnection(connectionString))
            using (MySqlCommand sqlCommand = sqlConnection.CreateCommand())
            {
                sqlCommand.CommandType = this.commandType;
                sqlCommand.CommandText = this.commandText;

                this.parameters.ForEach(p => sqlCommand.Parameters.Add(p));

                sqlConnection.Open();

                var reader = sqlCommand.ExecuteReader();
                while (reader.Read())
                {
                    yield return transformResults(new DynamicDataReader(reader));
                }
            }
        }

        public Database WithParameter(string parameter, object value)
        {
            this.parameters.Add(new MySqlParameter(parameter, value));
            return this;
        }
    }
}